from flask import Flask
from flask import flash
from flask import redirect
from flask import render_template
from flask import request
from flask import url_for
from flask_sqlalchemy import SQLAlchemy

class Config(object):
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = 'mysql://root:mysql@127.0.0.1:3306/flask_sql_ManagementOfBooks'
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SECRET_KEY = 'sdgsdngsondlks'

app = Flask(__name__)
app.config.from_object(Config)

db = SQLAlchemy(app)


# 用类来进行表的映射, 一个类就是一个表, 一个示例就是一条数据, 一个变量属性就是一个字段, 这里一定要继承db.Model
class Author(db.Model):
    __tablename__ = 'author'
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(128),unique=True)

    """
    relationship:
    添加到Author模型中的book属性代表这个关系的面向对象视角. 对于一个Author类的示例其book属性将返回与角色相关联的用户组成的列表
    db.relationship()第一个参数表明这个关系的另一端是哪个模型(类). 如果模型类尚未定义,可使用字符串形式指定
    db.relationship()第二个参数backref, 将向Book类中添加一个author属性,从未定义反向关系. 这一属性可替代author_id发访问Author模型,此时获取的是模型对象,而不是外键的值
    """
    books = db.relationship('Book',backref='author')

    def __repr__(self):
        return 'Author表: %s' % self.name

class Book(db.Model):
    __tablename__ = 'book'
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(128))
    au_book = db.Column(db.Integer,db.ForeignKey('author.id'))
    def __repr__(self):
        return 'Book表: %s' % self.name




@app.route('/',methods=['GET','POST'])
def hello():
    return "hello world"

@app.route('/index',methods=['GET','POST'])
def index():
    # 查询书籍和作者业务逻辑:GET
    if request.method == "GET":
        authors = Author.query.all()
        return render_template('temp01.html',authors=authors)
    # 添加书籍和作者业务逻辑:POST
    if request.method == 'POST':
        # 传入参数
        author_name = request.form.get('author')
        # 测试 request.form:   ImmutableMultiDict([('csrf_token', ''), ('author', 'laownag'), ('submit', '添加'), ('book', '')])
        print('测试 request.form:  ',request.form)
        # 测试 a  uthor_name:   laownag
        print('测试 author_name:  ',author_name)

        book_name = request.form.get('book')
        # 判断参数的完整性
        if not all([author_name,book_name]):
            flash('缺少必要参数')
            return redirect('/index')
        # 校验参数,all()是列表
        author = Author.query.filter(Author.name == author_name).first()
        print('测试 author:  ',author)

        if author:
            book = Book.query.filter(Book.name == book_name,Book.au_book == author.id).all()
            if book:
                """
                Flashes a message to the next request.  In order to remove the flashed message from the session and to display it to the user, the template has to call :func:`get_flashed_messages`.
                """
                flash('书籍已存在')
                # return redirect('/index')
            else:
                book = Book(name=book_name,au_book=author.id)
                try:
                    db.session.add(book)
                    db.session.commit()
                except Exception as e:
                    print(e)
                    db.session.rollback()
                    flash('添加失败')
        else:
            author = Author(name=author_name)
            book = Book(name=book_name)
            book.author = author
            try:
                db.session.add(book)
                db.session.commit()
            except Exception as e:
                print(e)
                db.session.rollback()
                flash("添加失败")
        return redirect('/index')


@app.route('/delete_author')
def delete_author():
    # 127.0.0.1 - - [25/Sep/2018 10:03:16] "GET /delete_author?aid=3 HTTP/1.1" 302 -      这里args指?后面的键值对,aid={{ author.id }}
    aid = request.args.get('aid')
    # 测试 request.args:   ImmutableMultiDict([('aid', '3')])
    print('测试 request.args:  ', request.args)
    print(aid)
    # 通过aid找到对应的作者记录
    author = Author.query.get(aid)
    if not author:
        # 感觉这个判断没用
        flash('作者不存在')
    else:
        try:
            Book.query.filter(Book.author_id == aid).delete()
            db.session.delete(author)
            db.session.commit()
        except Exception as e:
            print(e)
            db.session.rollback()
    return redirect(url_for('index'))


@app.route("/delete_book")
def delete_book():
    bid = request.args.get('bid')
    book = Book.query.get(bid)
    if book:
        try:
            db.session.delete(book)
            db.session.commit()
        except Exception as e:
            print(e)
            db.session.rollback()
    else:
        flash('书籍不存在')
    return redirect(url_for('index'))

if __name__ == "__main__":
    db.drop_all()
    db.create_all()

    au1 = Author(name='老王')
    au2 = Author(name='老尹')
    au3 = Author(name='老刘')
    db.session.add_all([au1, au2, au3])
    db.session.commit()
    bk1 = Book(name='老王回忆录', au_book=au1.id)
    bk2 = Book(name='我读书少，你别骗我', au_book=au1.id)
    bk3 = Book(name='如何才能让自己更骚', au_book=au2.id)
    bk4 = Book(name='怎样征服美丽少女', au_book=au3.id)
    bk5 = Book(name='如何征服英俊少男', au_book=au3.id)
    # 将数据委托给会话
    db.session.add_all([bk1,bk2,bk3,bk4,bk5])
    # 执行会话
    db.session.commit()

    app.run()
